﻿using NfsCommon;
using NfsCommon.DbHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NfsDAL
{
    /// <summary>
    /// 数据访问类:level
    /// </summary>
    public partial class LevelDAL
    {
        public LevelDAL()
        { }
        #region  Method

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperMySQL.GetMaxID("id", "t_level");
        }

        public int GetIdentityID(string tableName)
        {
            return DbHelperMySQL.GetIdentityID(tableName);

        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_level");
            strSql.Append(" where id=" + id + " ");
            return DbHelperMySQL.Exists(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(NfsModel.level model,out int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.parent_id != null)
            {
                strSql1.Append("parent_id,");
                strSql2.Append("" + model.parent_id + ",");
            }
            if (model.name != null)
            {
                strSql1.Append("name,");
                strSql2.Append("'" + model.name + "',");
            }
            if (model.code != null)
            {
                strSql1.Append("code,");
                strSql2.Append("'" + model.code + "',");
            }
            if (model.coding != null)
            {
                strSql1.Append("coding,");
                strSql2.Append("'" + model.coding + "',");
            }
            if (model.valid != null)
            {
                strSql1.Append("valid,");
                strSql2.Append("" + model.valid + ",");
            }
            if (model.description != null)
            {
                strSql1.Append("description,");
                strSql2.Append("'" + model.description + "',");
            }
            if (model.time != null)
            {
                strSql1.Append("time,");
                strSql2.Append("'" + model.time + "',");
            }
            strSql.Append("insert into t_level(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                id=GetIdentityID("t_level");
                return true;
            }
            else
            {
                id = -1;
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(NfsModel.level model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_level set ");
            if (model.parent_id != null)
            {
                strSql.Append("parent_id=" + model.parent_id + ",");
            }
          
            if (model.name != null)
            {
                strSql.Append("name='" + model.name + "',");
            }
           
            if (model.code != null)
            {
                strSql.Append("code='" + model.code + "',");
            }
           
            if (model.coding != null)
            {
                strSql.Append("coding='" + model.coding + "',");
            }
          
            if (model.valid != null)
            {
                strSql.Append("valid=" + model.valid + ",");
            }
            
            if (model.description != null)
            {
                strSql.Append("description='" + model.description + "',");
            }
           
            if (model.time != null)
            {
                strSql.Append("time='" + model.time + "',");
            }
            
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where id=" + model.id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_level ");
            strSql.Append(" where id=" + id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }		/// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_level ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NfsModel.level GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  ");
            strSql.Append(" id,parent_id,name,code,coding,valid,description,time ");
            strSql.Append(" from t_level ");
            strSql.Append(" where id=" + id + "");
            NfsModel.level model = new NfsModel.level();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NfsModel.level DataRowToModel(DataRow row)
        {
            NfsModel.level model = new NfsModel.level();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["parent_id"] != null && row["parent_id"].ToString() != "")
                {
                    model.parent_id = int.Parse(row["parent_id"].ToString());
                }
                if (row["name"] != null)
                {
                    model.name = row["name"].ToString();
                }
                if (row["code"] != null)
                {
                    model.code = row["code"].ToString();
                }
                if (row["coding"] != null)
                {
                    model.coding = row["coding"].ToString();
                }
                if (row["valid"] != null && row["valid"].ToString() != "")
                {
                    model.valid = int.Parse(row["valid"].ToString());
                }
                if (row["description"] != null)
                {
                    model.description = row["description"].ToString();
                }
                if (row["time"] != null && row["time"].ToString() != "")
                {
                    model.time = DateTime.Parse(row["time"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,parent_id,name,code,coding,valid,description,time ");
            strSql.Append(" FROM t_level ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM t_level ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperMySQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from t_level T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /*
        */

        #endregion  Method
        #region  自定义方法
        #region PC模糊查询
        public DataTable GetLevelBycondtion(int pageindex, int pagesize)
        {
            int startindex = (pageindex - 1) * pagesize;
            string strSql = string.Format(@"select top  "+pagesize+" * from (select ROW_NUMBER() over(order by id desc) as rownumber,id,parent_id,name,code,coding,valid,description,time  from (select le.*,lef.name as parentName from t_level as le left join t_level as lef on le.parent_id=lef.id) as a  where 1=1 and   valid=1 ");
            strSql += " ) as a where a.rownumber>"+startindex+" ";
            return DbHelperMySQL.Query(strSql).Tables[0];
        }
        public object GetLevelBycondtionCount()
        {
            string strSql = string.Format(@"select count(*) from t_level where  valid=1 ");
 
            return DbHelperMySQL.GetSingle(strSql);
        }
        #endregion
        #region 修改和添加用户判断用户信息是否有重复信息
        /// <summary>
        /// 重复判断
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="coding"></param>
        /// <returns></returns>
        public bool GetSingle(int id, string name, string coding)
        {

            StringBuilder strSql = new StringBuilder();
            if (id == 0)//表示添加
            {
                strSql.Append("select count(1) from t_level where valid=1 and (");
                strSql.Append(" name='" + name + "'");
                strSql.Append(" or coding='" + coding + "')" );
            }
            else //表示添加
            {
                strSql.Append("select count(1) from t_level where valid=1 and (");
                strSql.Append(" name='" + name + "'");
                strSql.Append(" or coding='" + coding + "')");
                strSql.Append(" and id!=" + id + " ");
            }
            return DbHelperMySQL.Exists(strSql.ToString());

        }
        #endregion

        #region 修改Code
        public bool UptLevel_code(int id, string Code)
        {
            string strSql = string.Format(@"update t_level set code='{0}'where id={1}", Code, id);
            int row = DbHelperMySQL.ExecuteSql(strSql);
            if (row > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
        #endregion  MethodEx
    }
}